package com.coolgor.coldot.common.database;

import com.coolgor.coldot.exceptions.database.C3p0OperationException;
import com.coolgor.coldot.exceptions.database.DatabaseOperationException;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.sql.*;
import java.util.List;


@Component
public class DatabaseOperation {

    private static Logger log = LoggerFactory.getLogger(DatabaseOperation.class);

    // 依赖注入连接池
    @Autowired
    ComboPooledDataSource dataSource;

    //从连接池中取用一个连接
    public Connection getConnection(){
        try {
            return dataSource.getConnection();
//            return dataSource.getConnectionPoolDataSource().getPooledConnection().getConnection();
        } catch (Exception e) {
            log.error("Exception in DatabaseOperation !", e);
            throw new C3p0OperationException("数据库连接出错!" + e);
        }
    }


    //释放连接回连接池 Statement
    private void close(Connection conn, Statement statement, ResultSet rs){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                log.error("Exception in DatabaseOperation !", e);
                throw new C3p0OperationException("数据库连接出错!" + e);
            }
        }
        if(statement!=null){
            try {
                statement.close();
            } catch (SQLException e) {
                log.error("Exception in DatabaseOperation!", e);
                throw new C3p0OperationException("数据库连接出错!" + e);
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                log.error("Exception in DatabaseOperation!", e);
                throw new C3p0OperationException("数据库连接出错!" + e);
            }
        }
    }


    //释放连接回连接池 PreparedStatement
    private void close(Connection conn, PreparedStatement pst, ResultSet rs){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                log.error("Exception in DatabaseOperation!", e);
                throw new C3p0OperationException("数据库连接出错!" + e);
            }
        }
        if(pst!=null){
            try {
                pst.close();
            } catch (SQLException e) {
                log.error("Exception in DatabaseOperation!", e);
                throw new C3p0OperationException("数据库连接出错!" + e);
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                log.error("Exception in DatabaseOperation!", e);
                throw new C3p0OperationException("数据库连接出错!" + e);
            }
        }
    }




    //注意：查询操作完成后，因为还需提取结果集中信息，所以仍保持连接，在结果集使用完后才通过DBUtils.releaseSource()手动释放连接
    public DataTable select(String sql) {
        Connection connection = this.getConnection();
        Statement statement = null;
        ResultSet rs = null;
        DataTable dt = null;
        try{
            statement = connection.createStatement();
            rs = statement.executeQuery(sql);
            if (null!=rs) {
                dt = new DataTable();
                dt.Create(rs);
            }
        } catch (SQLException e){
            log.error("SQL语法有误: ",e);
            throw new DatabaseOperationException("!!DatabaseUtil execute error : "+e);
        } finally {
            this.close(connection,statement,rs);
        }
        return dt ;
    }

    public DataTable select(String sql, List<String> list) {
        Connection connection = this.getConnection();
        PreparedStatement statement = null;
        ResultSet rs = null;
        DataTable dt = null;
        try{
            statement = connection.prepareStatement(sql);
            if (null!=list && list.size()>0){
                for (int i = 0; i < list.size(); i++) {
                    statement.setString(i+1, list.get(i));
                }
            }
            rs = statement.executeQuery();
            if (null!=rs) {
                dt = new DataTable();
                dt.Create(rs);
            }
        } catch (SQLException e){
            log.error("SQL语法有误: ",e);
            throw new DatabaseOperationException("!!DatabaseUtil execute error : "+e);
        } finally {
            this.close(connection,statement,rs);
        }
        return dt ;
    }

    //而update操作完成后就可以直接释放连接了，所以在方法末尾直接调用了
    public int update(String sql) {
        return update(sql, null);
    }

    public int update(String sql, List<String> list) {
        int result = 0;
        Connection connection = this.getConnection();
        PreparedStatement statement = null;
        try{
            statement = connection.prepareStatement(sql);
            if (null!=list && list.size()>0){
                for (int i = 0; i < list.size(); i++) {
                    statement.setString(i+1, list.get(i));
                }
            }
            result = statement.executeUpdate();
        } catch (SQLException e){
            log.error("SQL语法有误: ",e);
            throw new DatabaseOperationException("!!DatabaseUtil execute error : "+e);
        }
        this.close(connection,statement,null);
        return result ;
    }
}